IF OBJECT_ID('dbo.vwAgreementClinicalTrialBudget') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwAgreementClinicalTrialBudget
    IF OBJECT_ID('dbo.vwAgreementClinicalTrialBudget') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwAgreementClinicalTrialBudget >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwAgreementClinicalTrialBudget >>>'
END
GO
-- =======================================================================
-- Object Name: (VIEW) dbo.vwAgreementClinicalTrialBudget
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 01/08/2008
--
-- Description: View to get breakout of Budget Totals for ClinicalTrials,
--        including AgreementId, InPatientCareTotal, ExpectedEnrollment,
--        and TotalDirectCost.
--
--        A change for FY08 necessitates this because going forward, 
--        InPatientCareTotal will be entered on a Per Patient basis, rather
--        than as a total. OneTimeFees are derived.
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 01/08/08    KC        Created
-- 01/11/08    KC        Bug fix, still had an AgreementID buried in the 
--                       code! And had an extra "e" in name!
--
-- =======================================================================
CREATE VIEW dbo.vwAgreementClinicalTrialBudget
AS

-- THIRD QUERY: Gets Totals for Fees into one line and joins with
-- other sources for remaining data fields.
SELECT    AB.AgreementId AS AgreementID,
          SUM(AB.BudgetDirectCost) AS ProjectDirectCost,
          SUM(B.InPatientCareTotal) AS InPatientCareTotal,
          SUM(B.IRBFeesExpense) AS IRBFeesExpense,
          SUM(AB.BudgetDirectCost) - SUM(B.InPatientCareTotal) AS OneTimeFees,
          UDF.TargetNumberofPatients AS ExpectedPatientEnrollment,
          MAX(AB.IndirectCostRate) AS IndirectCostRate
FROM      dbo.AgrAgreement_Budget AB 
          INNER JOIN dbo.AgrAgreement_Info AI ON AB.AgreementID = AI.AgreementID
          LEFT OUTER JOIN dbo.InfoEdUDFProposal UDF ON AI.InfoEdPropNumber = UDF.PropNumber
          LEFT OUTER JOIN (
               -- This flattens out the records to one
               SELECT    BB.AgreementID,
                         MAX(InPatientCareTotal) AS InPatientCareTotal,
                         MAX(IRBFeesExpense) AS IRBFeesExpense               
               FROM
               -- This SubQuery Calculates InPatientCareTotal, and IRBExpenses from BudgetFact table.
               -- SECOND QUERY join gets the InPatientCareTotal and IRBExpenses
               (SELECT    BUDG.AgreementID,
                         CASE WHEN TM.AgrAccountTemplateId = 7 
                             THEN SUM(BUDG.BudgetAmount)
                             ELSE NULL
                         END AS InPatientCareTotal,
                         CASE WHEN TM.AgrAccountTemplateId = 72 
                             THEN SUM(BUDG.BudgetAmount)
                             ELSE NULL
                         END AS IRBFeesExpense
               FROM      dbo.AgrBudget_Fct BUDG JOIN dbo.AgrBudgetItem BI ON BUDG.BudgetItemId = BI.ID 
                         JOIN dbo.AgrBudgetItem_ContainerAccount_Template_Map TM ON BI.AgrContainerAccountTemplateMapId = TM.ID
                              AND TM.AgrAccountTemplateId IN (7,72)
                    INNER JOIN (
                        -- REFERRED TO AS FIRST INNER QUERY! 
                        -- This gets the "BEST" BudgetType for each AgreementId.
                        SELECT    B.AgreementId,
                                  CASE WHEN A.MasterProposalStatus = 'Pending'
                                      THEN 'Pending'
                                      ELSE MIN(CASE B.BudgetViewTypeId 
                                           WHEN 3 THEN 'Awarded'
                                           WHEN 6 THEN 'Committed'
                                           WHEN 1 THEN 'Pending'
                                      END )
                                  END AS BudgetViewType 
                        FROM      dbo.AgrBudget_Fct B JOIN dbo.AgrAgreement_Info A ON B.AgreementId = A.AgreementId
                        WHERE     B.IsAdjustment = 0 
                        GROUP BY  B.AgreementId, 
                                  A.MasterProposalStatus) BP ON BUDG.AgreementID = BP.AgreementID 
                                        AND CASE BUDG.BudgetViewTypeId 
                                            WHEN 3 THEN 'Awarded' 
                                            WHEN 6 THEN 'Committed' 
                                            WHEN 1 THEN 'Pending' 
                                        END = BP.BudgetViewType
               GROUP BY  BUDG.AgreementID, 
                         TM.AgrAccountTemplateId ) BB
               GROUP BY  BB.AgreementID                      
               ) B ON AB.AgreementID = B.AgreementID
GROUP BY AB.AgreementID, UDF.TargetNumberofPatients


GO

IF OBJECT_ID('dbo.vwAgreementClinicalTrialBudget') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwAgreementClinicalTrialBudget >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwAgreementClinicalTrialBudget >>>'
GO
        